package service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class ServiceSql {
	String dburl = "jdbc:mysql://localhost:3306/Androidgame";
	String user = "root";
	String password ="123456";
	String driver = "com.mysql.jdbc.Driver";
	ResultSet rs = null;
	Connection conn = null;
	PreparedStatement ps =null;
	String str;
	
	public ServiceSql(){
		try{
			Class.forName(driver);
			conn  = DriverManager.getConnection(dburl,user,password);
		}catch(Exception e){
			System.out.print(e.getMessage());
		}
	}
	
	
	public  synchronized ArrayList<String> getPlayer(String player){
		ArrayList<String> ans =  new ArrayList<String>();
		try{
			String sql = "select * from player where pname=? ;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, player);
			rs = ps.executeQuery();
			while(rs.next()){
				str = "photo ";
				str += Integer.toString(rs.getInt("photo"));
				ans.add(str);
				
				str = "gold ";
				str += Integer.toString(rs.getInt("gold"));
				ans.add(str);	
			}
			
			sql = "select * from pp where pname=? ;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, player);
			rs = ps.executeQuery();
			str = "pet";
			while(rs.next()){
				str +=" "+rs.getString("pet");
				str +=" "+rs.getString("used");
			}
			ans.add(str);
			
			sql = "select * from pm where pname=? ;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, player);
			rs = ps.executeQuery();
			str = "mission";
			while(rs.next()){
				str +=" "+rs.getString("mission");
			}
			ans.add(str);
			
			sql = "select * from pc where pname=? ;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, player);
			rs = ps.executeQuery();
			str = "card";
			while(rs.next()){
				str += " "+rs.getString("card");
				str += " "+rs.getString("used");
			}
			ans.add(str);
			
			ps.close();
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		if(ans.size()!=5) return null;
		return ans;
	}
	
	public synchronized void update(String in){
		String[] temp = in.split(" ");
		String sql = null;
		try{
			if(temp[1].equals("gold")){
				sql = "update player set gold=? where pname=?;";
				ps=conn.prepareStatement(sql);
				ps.setString(1, temp[2]);
				ps.setString(2, temp[0]);
			}
			else if(temp[1].equals("photo")){
				sql = "update player set photo=? where pname=?;";
				ps=conn.prepareStatement(sql);
				ps.setString(1, temp[2]);
				ps.setString(2, temp[0]);
			}else if(temp[1].equals("card")){
				sql = "update pc set used=? where pname=? and card = ?;";
				ps=conn.prepareStatement(sql);
				ps.setString(1, temp[3]);
				ps.setString(2, temp[0]);
				ps.setString(3, temp[2]);
			}
			else if(temp[1].equals("pet")){
				sql = "update pp set used=? where pname=? and pet = ?;";
				ps=conn.prepareStatement(sql);
				ps.setString(1, temp[3]);
				ps.setString(2, temp[0]);
				ps.setString(3, temp[2]);
			}
			ps.executeUpdate();
			ps.close();
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		
	}
	public synchronized void insert(String in){
//		System.out.println(in);
		String[] temp = in.split(" ");
		String sql = null;
		try{
			if(temp[1].equals("pet")) sql="insert into pp values(?,?,0);";
			else if(temp[1].equals("card")) sql="insert into pc values(?,?,0);";
			else if(temp[1].equals("mission")) sql="insert into pm values(?,?);";
			
//			System.out.println(sql);
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, temp[0]);
			ps.setString(2,temp[2]);	
			ps.executeUpdate();
			ps.close();
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}
	
	public synchronized void delete(String in){
		String[] temp = in.split(" ");
		String sql = null;
		try{
			if(temp[1].equals("pet")) sql = "delete  from pp where pname=? and pet=? ;";
			else if(temp[1].equals("card")) sql = "delete  from pc where pname=? and card=? ;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, temp[0]);
			ps.setString(2,temp[2]);
			
			ps.executeUpdate();
			
			ps.close();
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
	}
	public synchronized boolean logIn(String inform){
		String player =null, password = null;
		String[] temp = inform.split(" ");
		player = temp[0];
		password = temp[1];
//		System.out.println(player);
//		System.out.println(password);
		
		String sql = "select * from player where pname= ? and password= ? ;";
//		System.out.println(sql);
		
		try{
			ps = conn.prepareStatement(sql);
			ps.setString(1, player);
			ps.setString(2, password);
			rs = ps.executeQuery();
			while(rs.next()){
				ps.close();
				return true;
			}
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		return false;
	}
	public synchronized boolean exist(String player){
		String sql = "select * from player where pname = ? ;";
		try{
			ps = conn.prepareStatement(sql);
			ps.setString(1, player);
			rs = ps.executeQuery();
			while(rs.next()){
				ps.close();
				return true;
			}
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		return false;
	}
	
	public synchronized boolean register(String inform){
		String player = null,photo =null,password=null,gold = null;
		String temp[] = inform.split(" ");
		player = temp[0];
		
		if(exist(player)) return false;
		
		
		password = temp[1];
		photo = temp[2];
		gold = temp[3];
		
		String sql = "insert into player (pname,password,photo,gold) values(?,?,?,?);";
		try{
			ps = conn.prepareStatement(sql);
			ps.setString(1, player);
			ps.setString(2, password);
			ps.setInt(3, Integer.parseInt(photo));
			ps.setInt(4,Integer.parseInt(gold));
			ps.executeUpdate();
			
			ps.close();
//			System.out.print(b)
			
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			
		}
		return false;
		
	}
}
